import random
import pymysql
import datetime

class utility_function():
    def __init__(
            self,
            username: str,
            password: str,
            host: str,
            d: str,
            port: int =3306
    ) -> None:
        self.con = pymysql.connect(
            user=username,
            password=password,
            host=host,
            database=d,
            port=port
        )
    def jinhuo(self, bookname: str):
        try:
            cur = self.con.cursor()
            if bookname == "":
                return False, "请输入要进货的书名"

            # 1.从书本表中找到该书
            sqlsentence = """select * from book where name='{}';""".format(bookname)
            qnum = cur.execute(sqlsentence)
            qret = cur.fetchall()

            # 2.根据当前库存和月销售量确定进货数量
            kuCun = qret[0][2] if qnum != 0 else 0
            yveXiaoShouLiang = 0
            sqlsentence = """select sum(remainNumber) from xiaoshoudan where month(dt)=month(now());"""
            flag = cur.execute(sqlsentence)
            ret = cur.fetchall()
            yveXiaoShouLiang = int(ret[0][0]) if ret[0][0] else 0
            # yveXiaoShouLiang = 100
            jinhuoNum = 0
            if yveXiaoShouLiang <= kuCun:
                jinhuoNum = int((yveXiaoShouLiang + kuCun) * 0.9 - kuCun)
            elif kuCun < yveXiaoShouLiang <= 2 * kuCun:
                jinhuoNum = int((yveXiaoShouLiang + kuCun) * 1.1 - kuCun)
            elif 2 * kuCun < yveXiaoShouLiang:
                jinhuoNum = int((yveXiaoShouLiang + kuCun) * 1.2 - kuCun)
            jinhuoNum = 0 if jinhuoNum < 0 else jinhuoNum
            if jinhuoNum == 0:
                return False, "本月该书本销售量远小于剩余库存，不需要进货"

            # 3.选择供应商，确定进货价钱和销售单价
            sqlsentence = """select * from baojiabiao where bookname='{}' and 
                            price = (select min(price) from baojiabiao where bookname='{}');""" \
                .format(bookname, bookname)
            snum = cur.execute(sqlsentence)
            sret = cur.fetchall()

            if snum == 0:
                return False, "没有该书的供应商"

            choosegongyingshang = random.randint(0, snum - 1)
            gongyingshangID = sret[choosegongyingshang][0]
            baojia = sret[choosegongyingshang][2]
            unitPrice = baojia * 1.5

            # 4.根据进货数量和新的进货价钱以及销售单价修改书本表记录
            if qnum == 0:
                # print("\'{}\'的库存量为: 0".format(bookname))
                sqlsentence = """insert into book values (null, '{}', {}, {} );""".format(bookname, jinhuoNum,
                                                                                          unitPrice)
                cur.execute(sqlsentence)

                sqlsentence = """select * from book where name='{}';""".format(bookname)
                cur.execute(sqlsentence)
                qret = cur.fetchall()
            else:
                # print("\'{}\'的库存量为: {}".format(bookname, qret[0][2]))
                sqlsentence = """update book set number = number + {}, unitPrice = {} where name='{}'""". \
                    format(jinhuoNum, unitPrice, bookname)
                cur.execute(sqlsentence)

            # 5.添加进货记录
            sqlsentence = """insert into jinHuoDan values (null, {}, {}, now(), {}, {});""" \
                .format(qret[0][0], gongyingshangID, jinhuoNum, baojia)
            cur.execute(sqlsentence)
            sqlsentence = """select * from jinhuodan where JID >= all (select JID from jinhuodan);"""
            qnum = cur.execute(sqlsentence)
            qret = cur.fetchall()

            self.con.commit()
            cur.close()
            return True, qret[0]
        except Exception as e:
            self.con.rollback()
            return False, e

    def tuihuo(self,xiaoshouid:int,num:int) -> [bool,int]:
        try:
            cur = self.con.cursor()
            sqlsentence = 'select * from xiaoShouDan where XID=' + str(xiaoshouid) + ";"
            flag = cur.execute(sqlsentence)
            ret = cur.fetchall()
            if(flag==0 or num<=0):
                return [False,0]
            remain_num=ret[0][6]
            if(remain_num<num):
                return [False,0]
            remain_num=remain_num-num
            sqlsentence = 'update xiaoShouDan set remainNumber=' + str(remain_num) + ' where XID=' + str(
                xiaoshouid) + ";"
            cur.execute(sqlsentence)
            sqlsentence = 'insert into tuiHuoDan(TID,BID,CID,dt,number) values(null,' + str(ret[0][1]) + ',' + str(
                ret[0][2]) + ',now(),' + str(num) + ");"
            cur.execute(sqlsentence)
            sqlsentence = 'update book set number=number+' + str(num) + ' where BID=' + str(ret[0][1]) + ';'
            cur.execute(sqlsentence)
            sqlsentence = 'select TID from tuiHuoDan where TID>=all(select TID from tuiHuoDan);'
            cur = self.con.cursor()
            cur.execute(sqlsentence)
            ret = cur.fetchall()
            self.con.commit()
            cur.close()
            return [True, ret[0][0]]
        except Exception as e:
            self.con.rollback()
            cur.close()
            return [False,0]

    def xiaoshou(self,bookname:str,num:int,cid:int)->[bool,int]:
        try:
            cur = self.con.cursor()
            sqlsentence = 'select * from book where name=\'' + str(bookname) + '\';'
            flag = cur.execute(sqlsentence)
            ret = cur.fetchall()
            if (flag == 0 or num<=0):
                cur.close()
                return [False,0]
            number=ret[0][2]
            if(number<num):
                cur.close()
                return [False,0]
            number=number-num
            sqlsentence = 'insert into xiaoShouDan(XID,BID,CID,dt,unitPrice,number,remainNumber) values(null,' + str(
                ret[0][0]) + ',' + str(cid) + ',now(),' + str(ret[0][3]) + ',' + str(num) + ',' + str(num) + ');'
            cur.execute(sqlsentence)
            sqlsentence = 'update book set number=' + str(number) + ' where BID=' + str(ret[0][0]) + ';'
            cur.execute(sqlsentence)
            sqlsentence = 'select XID from xiaoShouDan where XID>=all(select XID from xiaoShouDan);'
            cur.execute(sqlsentence)
            ret = cur.fetchall()
            self.con.commit()
            cur.close()
            return [True, ret[0][0]]
        except Exception as e:
            self.con.rollback()
            cur.close()
            return [False,0]

    def kucun(self,bookname:str)->[bool,int]:
        try:
            cur = self.con.cursor()
            sqlsentence = 'select number from book where name=\'' + str(bookname) + '\';'
            flag = cur.execute(sqlsentence)
            if (flag == 0):
                return [False, 0]
            ret = cur.fetchall()
            cur.close()
            return [True, ret[0][0]]
        except:
            return [False,0]

    def printxid(self,xid:int):
        try:
            sqlsentence = 'select * from xiaoShouDan where XID=' + str(xid) + ';'
            cur = self.con.cursor()
            flag = cur.execute(sqlsentence)
            ret = cur.fetchall()
            cur.close()
            return ret[0]
        except Exception as e:
            return 0

    def printtid(self,tid:int):
        try:
            sqlsentence = 'select * from tuihuodan where tid=' + str(tid) + ';'
            cur = self.con.cursor()
            flag = cur.execute(sqlsentence)
            ret = cur.fetchall()
            cur.close()
            return ret[0]
        except Exception as e:
            return 0

    def monthofbook(self,m:str):
        try:
            cur = self.con.cursor()
            year = str(datetime.datetime.now().year)
            sql1 = """select bid,sum(remainNumber),unitPrice*sum(remainNumber) as totalprices from xiaoshoudan where  dt between '"""
            sql2 = """-01' and '"""
            sql3 = """-31' group by bid order by sum(remainNumber)*unitPrice desc """
            sql = ""
            # 所有月份
            if (m == ""):
                m1 = '1'
                m2 = '12'
                sql = sql1 + year + '-' + m1 + sql2 + year + '-' + m2 + sql3
            else:
                sql = sql1 + year + '-' + m + sql2 + year + '-' + m + sql3
            cur.execute(sql)
            result = cur.fetchall()
            resultlist = list(result)
            for i in range (0,len(resultlist)):
                resultlist[i] = list(resultlist[i])
                resultlist[i].insert(0,i+1)
                booksql = """ select name from book where bid = """ + str(resultlist[i][1])
                cur.execute(booksql)
                bookresult = cur.fetchall()
                bookresult=bookresult[0][0]
                del resultlist[i][1]
                resultlist[i].insert(1,bookresult)
                tmp = int(resultlist[i][2])
                del resultlist[i][2]
                resultlist[i].insert(2,tmp)
                del resultlist[i][0]
            cur.close()
            return resultlist
        except Exception as e:
            return []

    def close(self):    # 关闭连接
        self.con.close()
